package birs.controller.quality.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.quality.QLT001Model;
import birs.model.quality.QLT001ModelA;
import birs.model.quality.QLT001ModelB;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapperForMysql;

public class QLT001Task {
	
	private Connection con = null;
	
	public void getQuality(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException{
		ResultSetMapperForMysql<QLT001Model> qltPoint = new ResultSetMapperForMysql<QLT001Model>();
		List<QLT001Model> qltPointList = qltPoint.mapRersultSetToObject(getQualitySQL(), QLT001Model.class);
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(qltPointList);
		sendResponse(request, response, resultJson);
		
	}
	
	public void getQualityByFac(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException{
		ResultSetMapperForMysql<QLT001ModelA> qltFacPoint = new ResultSetMapperForMysql<QLT001ModelA>();
		List<QLT001ModelA> qltFacPointList = qltFacPoint.mapRersultSetToObject(getFacQualitySQL(), QLT001ModelA.class);
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(qltFacPointList);
		sendResponse(request, response, resultJson);
		
	}
	
	public void getComment(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException{
		ResultSetMapperForMysql<QLT001ModelB> qltComment = new ResultSetMapperForMysql<QLT001ModelB>();
		List<QLT001ModelB> qltCommentList = qltComment.mapRersultSetToObject(getCommentSQL(request.getParameter("qltElement"), request.getParameter("acadYear")), QLT001ModelB.class);
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(qltCommentList);
		sendResponse(request, response, resultJson);
		
	}
	
	
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
		// Release JDBC pool connection after sent result to client
		releaseConnection();
	}
	
	private ResultSet getQualitySQL() throws SQLException{
		String sql = "SELECT QualityElement, AcadYear, 	"
				+" 	Point, Strength, Weak	"
				+" 	FROM qa_universityelementpoint ORDER BY QualityElement";

		return getData(sql);
	}
	
	private ResultSet getFacQualitySQL() throws SQLException{
		String sql = " 	SELECT qfac.FacultyId, fac.fac_name_th, 	"
				+" 	qfac.Year , qfac.Point	"
				+" 	FROM qa_facultyelementpoint qfac, faculty fac	"
				+" 	WHERE qfac.FacultyId = fac.fac_id	"
				+" 	ORDER BY qfac.FacultyId, qfac.Year ";
		return getData(sql);
	}
	
	private ResultSet getCommentSQL(String qltElement,String acadYear) throws SQLException{
		String sql = " 	SELECT Strength, Weak	"
				+" 	FROM qa_universityelementpoint 	"
				+" 	WHERE QualityElement = "+qltElement
				+" 	AND AcadYear ="+acadYear;
		return getData(sql);
	}
	
	private ResultSet getData(String sql) throws SQLException {
		ResultSet result = null;
		
		try {
			ConnectionDB.getInstance();
			con = ConnectionDB.getRBRUMySQL();
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private void releaseConnection(){
		if(con != null){
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace(System.err);
			}
		}
	}
}
